import pandas as pd

import numpy as np

import matplotlib.pyplot as plt

import seaborn as sns

df=pd.read_excel('C:\FormMyConputer\Data Analysis\Finance_Data_Analysis/Bank_Personal_Loan_Modelling.xlsx',1)

df.head()

df.shape

df.isnull().sum()

# ID and Zipcode might be removed as they may not be useful for our analysis

​

df.drop(['ID','ZIP Code'],axis=1,inplace=True)

df.columns

import plotly.express as px

fig=px.box(df,y=['Age', 'Experience', 'Income', 'Family', 'Education'])

fig.show()

df.skew()

df.dtypes

df.hist(figsize=(20,20))

import seaborn as sns

sns.distplot(df['Experience'])

df['Experience'].mean()

Negative_exp=df[df['Experience']<0]

Negative_exp.head()

sns.distplot(Negative_exp['Age'])

Negative_exp['Experience'].mean()

Negative_exp.size

print('There are {} records which has negative values for experience, approx {} %'.format(Negative_exp.size , ((Negative_exp.size/df.size)*100)))

data=df.copy()

data.head()

​

data['Experience']=np.where(data['Experience']<0,data['Experience'].mean(),data['Experience'])

data[data['Experience']<0]

plt.figure(figsize=(10,6))

sns.heatmap(df.corr(),annot=True)

data=data.drop(['Experience'],axis=1)

data.head()

​

data['Education'].unique()

def mark(x):

    if x==1:

        return 'Undergrad'

    elif x==2:

        return 'Graduate'

    else:

        return 'Advanced/Professional'

data['Edu_mark']=data['Education'].apply(mark)

data.head()

EDU_dis=data.groupby('Edu_mark')['Age'].count()

EDU_dis

fig=px.pie(data,values=EDU_dis, names=EDU_dis.index,title='Pie CHart')

fig.show()

​

data.columns

def Security_CD(row):

    if (row['Securities Account']==1) & (row['CD Account']==1):

        return 'Holds Securites & Deposit'

    elif (row['Securities Account']==0) & (row['CD Account']==0):

        return 'Does not Holds Securites or Deposit'

    elif (row['Securities Account']==1) & (row['CD Account']==0):

        return ' Holds only Securites '

    elif (row['Securities Account']==0) & (row['CD Account']==1):

        return ' Holds only Deposit'

data['Account_holder_category']=data.apply(Security_CD,axis=1)

data.head()

values=data['Account_holder_category'].value_counts()

values.index

​

fig=px.pie(data,values=values, names=values.index,title='Pie CHart')

fig.show()

​

data.columns

px.box(data,x='Education',y='Income',facet_col='Personal Loan')

​

plt.figure(figsize=(12,8))

sns.distplot(data[data['Personal Loan']==0]['Income'],hist=False,label='Income with no personal loan')

sns.distplot(data[data['Personal Loan']==1]['Income'],hist=False,label='Income with personal loan')

plt.legend()

​

def plot(col1,col2,label1,label2,title):

    plt.figure(figsize=(12,8))

    sns.distplot(data[data[col2]==0][col1],hist=False,label=label1)

    sns.distplot(data[data[col2]==1][col1],hist=False,label=label2)

    plt.legend()

    plt.title(title)

plot('Income','Personal Loan','Income with no personal loan','Income with personal loan','Income Distribution')

​

plot('CCAvg','Personal Loan','Credit card avg with no personal loan','Credit card avg with personal loan','Credit card avg Distribution')

plot('Mortgage','Personal Loan','Mortgage of customers with no personal loan','Mortgage of customers  with personal loan','Mortgage of customers  Distribution')

​

data.columns

col_names=['Securities Account','Online','Account_holder_category','CreditCard']

​

for i in col_names:

    plt.figure(figsize=(10,5))

    sns.countplot(x=i,hue='Personal Loan',data=data)

​

sns.scatterplot(data['Age'],data['Personal Loan'],hue=data['Family'])

import scipy.stats as stats

Ho='Age does not have impact on availing personal loan'

Ha='Age does  have impact on availing personal loan'

Age_no=np.array(data[data['Personal Loan']==0]['Age'])

Age_yes=np.array(data[data['Personal Loan']==1]['Age'])

t,p_value=stats.ttest_ind(Age_no,Age_yes,axis=0)

if p_value<0.05:

    print(Ha,' as the p_value is less than 0.05 with a value of {}'.format(p_value))

else:

    print(Ho,' as the p_value is greater than 0.05 with a value of {}'.format(p_value))

​

def Hypothesis(col1,col2,HO,Ha):

    arr1=np.array(data[data[col1]==0][col2])

    arr2=np.array(data[data[col1]==1][col2])

    t,p_value=stats.ttest_ind(arr1,arr2,axis=0)

    if p_value<0.05:

        print('{}, as the p_value is less than 0.05 with a value of {}'.format(Ha,p_value))

    else:

        print('{} as the p_value is greater than 0.05 with a value of {}'.format(HO,p_value))

Hypothesis('Personal Loan','Age',HO='Age does not have impact on availing personal loan',Ha='Age does  have impact on availing personal loan')

​

Hypothesis(col1='Personal Loan',col2='Income',HO='Income does not have impact on availing personal loan',Ha='Income does  have impact on availing personal loan')

​

Hypothesis('Personal Loan','Family',HO='AgFamily does not have impact on availing personal loan',Ha='Family does  have impact on availing personal loan')

​